package com.max.spark.mysql

import com.max.spark.utils.{ConfUtil, LogUtil}

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}

object ScalaJdbcMySQLApp {
  val confPath = "conf/mysql.properties"
  val hostname = ConfUtil.getProperty(confPath, "mysql.hostname")
  val port = ConfUtil.getProperty(confPath, "mysql.port")
  val username = ConfUtil.getProperty(confPath, "mysql.username")
  val password = ConfUtil.getProperty(confPath, "mysql.password")
  val driver = ConfUtil.getProperty(confPath, "mysql.driver")
  val db = ConfUtil.getProperty(confPath, "mysql.db")
  val table = ConfUtil.getProperty(confPath, "mysql.table")

  // 添加属性
  val url = s"jdbc:mysql://$hostname:$port/$db"

  var conn:Connection = null
  var pst:PreparedStatement = null
  var rs:ResultSet = null

  def getConnection():Unit = {
    Class.forName(driver);
    conn = DriverManager.getConnection(url,username,password)
  }

  // 增删改操作
  // 参数为需要执行的sql语句，obj参数数量不固定，按需传递
  def update(sql: String, obj: Any*):Int = {
    val num = try {
      // 创建连接
      getConnection()
      // 创建prepareStatement对象
      pst = conn.prepareStatement(sql)
      // 循环给赋值
      for(i <- 0 until obj.length){
        pst.setObject(i+1, obj(i))
      }
      pst.executeUpdate()
    } catch {
      case e: Exception => {
        LogUtil.error(e.toString) //在try后打印异常信息,然后将None作为返回值返回
        0
      }
    }
    pst.close()
    conn.close()
    num
  }

  // 查询全部信息
  def select()={
    getConnection()
    val sql = "select name, age from myuser"
    pst = conn.prepareStatement(sql)
    rs = pst.executeQuery()
    while(rs.next()){
      val name = rs.getString("name")
      val age = rs.getInt("age")
      println(name+" "+age)
    }
    rs.close()
    pst.close()
    conn.close()
  }

  def main(args: Array[String]): Unit = {
    // 插入
    val sql = "insert into myuser(name, age) values(?,?)"
    println(update(sql, "张三", 25))
    // 删除
    val sql2 = "delete from myuser where name = ?"
    println(update(sql2, "lisi"))
    // 修改
    val sql3 = "update myuser set name=? where age=?"
    println(update(sql3,"小梅", 16))
    // 查询
    select()
  }


}
